'''
第三周作業
匯入自建的資料庫模塊module.model_sql 用來連接Mysql
匯入datetime模塊底下的datetime 用來記錄交易發生時間

連接資料庫, 預設連進mysql
建立並使用資料庫, atm_db
建立使用者資料表, atm_users 字段(id,password,name,balance,cdate)
插入使用者資料,    													******參考此處ATM使用者帳號及密碼******
建立方法def checkid()當用戶登入帳戶時調用，確認身分。
建立方法def is_number()當存款、取款輸入時，確認是否為數字。
建立方法def table_update()當完成存款、款業務後呼叫，更新存款欄位並將數據插入個人歷史交易紀錄。
建立方法def show_transaction()用來顯示使用者歷史交易紀錄。
主程式 調用以上
'''


#gosql.sql = '''create database ATM_db'''
#gosql.exe(True)
#gosql.sql = '''use ATM_db'''
#gosql.exe(True)
#gosql.sql = '''select database()'''
#gosql.exe(True)
#===============================================================================================================
# gosql.sql = '''
# CREATE TABLE atm_users (
  # `Id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID號',
  # `Password` varchar(8) NOT NULL COMMENT '用戶名',
  # `name` varchar(100) NOT NULL COMMENT '用戶名',
  # `balance` decimal(11,2) NOT NULL COMMENT '存款',
  # `cdate` datetime NOT NULL COMMENT '註冊時間',
  # PRIMARY KEY (`Id`)
# ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
# '''
# gosql.create()
#===============================================================================================================
# table_name = "atm_users"
# tup1 = "id","password","name","balance","cdate"  #tuple for fields
# tup2 = '''
# ("1","11111111","Monica","500","2019-2-14 16:30:10"),
# ("2","22222222","Ross","7000","2019-2-16 14:21:02"),
# ("3","33333333","Chandler","4500","2019-3-16 10:11:22"),
# ("4","44444444","Reachel","380","2019-1-16 08:12:24"),
# ("5","55555555","Pheebe","9000","2019-1-12 14:00:04"),
# ("6","66666666","Joey","8800","2019-2-5 13:50:14")
# '''  #tuple for values
# gosql.insert(table_name,tup1,tup2)
#===============================================================================================================
from module.model_sql import *   #from model_sql import Sql_connect class
from datetime import datetime
import time


gosql = Sql_connect()
gosql.sql = '''use ATM_db'''
gosql.exe()
gosql.sql = '''select database()'''
gosql.exe()
print("\n\n\nDatabase changed, you are now using database : "+str(gosql.data[0][0])+"\n\n")


'''identify users'''
def checkid(user_name_,user_pwd_):
	#get name list from database()
	gosql.sql = "select name = '"+user_name_+"', password from atm_users where name = '"+user_name_+"'"
	gosql.exe() #self.data has had  .... use comparsion "is null or is not null" , MYSQL has variables could temporary store values.

	if gosql.data == ():
		print("Sorry, We can't find the account in system.")
		return False
		
	elif gosql.data[0][0] and gosql.data[0][1] == user_pwd_:
		gosql.sql = "select id, name,balance from atm_users where name = '"+user_name_+"'"
		gosql.exe()
		return True
		
	else:
		print("Incorrect id or password")
		return False
		
'''Check numbers'''
def is_number(c):
	try:
		float(c)
		return True
	except:
		return False

'''update balance and insert into transaction'''
def table_update(switch_,amount_,user_id_,user_name_,user_balance_,time_ = datetime.now().strftime("%Y-%m-%d %H:%M:%S")):
	#update atm_users's balance
	gosql.update("atm_users","balance",str(user_balance),"id ='"+user_id+"'")  
	
	#create, if there isn't one, a personal table for documenting user's transaction log etc.. 
	if gosql.table_exist(str(user_id_)+"_"+str(user_name_)+"_transaction_log"):
		pass
		
	else:
		gosql.sql = '''	
		CREATE TABLE `'''+user_id_+'''_'''+user_name_+'''_transaction_log` (
		  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
		  `withdraw` float(7,2) DEFAULT NULL,
		  `deposit` float(7,2) unsigned DEFAULT NULL,
		  `balance` float(11,2) NOT NULL,
		  `date` datetime NOT NULL,
		  PRIMARY KEY (`id`)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
		'''
		gosql.exe()
	
	
	if switch_ == "w":  #insert withdraw log to user's transaction log
		
		gosql.insert(user_id_+"_"+user_name_+"_transaction_log",("withdraw","balance","date"),(amount_,user_balance_,time_),True) #withdraw
	
	elif switch_ == "d":  #insert deposit log to user's transaction log
		
		gosql.insert(user_id_+"_"+user_name_+"_transaction_log",("deposit","balance","date"),(amount_,user_balance_,time_),True) #deposit

'''show transaction history'''
def show_transaction(user_name_,user_id_):

		#if transaction table exists, fetch and print
		if gosql.table_exist(str(user_id_)+"_"+str(user_name_)+"_transaction_log"):
			gosql.sql = "select * from "+user_id_+"_"+user_name_+"_transaction_log"
			gosql.exe()
			print("|      datetime      |   withdraw   |    deposit   |       balance      |")
			
			#replace whatever is None to empty string 
			f = []
			for i in gosql.data:
				f.append(list(i))
			for i in f:
				for j in range(0,len(i)-1):
					if i[j] is None:
						i[j] = ""
				print("{0:1}{1:<20}{0:1}{2:^14}{0:1}{3:^14}{0:1}{4:>20}{0:1}".format("|",str(i[4]),str(i[1]),str(i[2]),str(i[3]))) 
			print("\n- End")
			input("Press any key to return")
				
		else:
			print("You have no transaction history yet.")
			input("Press any key to return")
			
		
		
''''main '''
while True:
	#login
	user_name = input("Please enter your English name : ")
	user_pwd = input("Please enter your password ： ")
	if checkid(user_name,user_pwd):
		user_id = str(gosql.data[0][0])
		user_name = gosql.data[0][1]
		user_balance = float(gosql.data[0][2])
		print("\n"*50+"Welcome "+user_name+" !\n")
		break
	else:
		continue
	
while True:
	#in system
	print("\n"*3+"="*20+"ATM GUI"+"="*20)
	a = len("="*20+"ATM GUI"+"="*20)
	print("{0:5}{1:<15}{2:<15}{0:5}".format(" ","1.Balance","2.Withdraw"))
	print("{0:5}{1:<15}{2:<15}{0:5}".format(" ","3.Deposit","4.Exit"))
	print("{0:5}{1:<15}{2:<15}{0:5}".format(" ","5.History"," "))
	print("="*a)
	
	b = input("Please Enter the Number Indicating Above : ")
	
	#if 1 = balance, 2 = withdraw, 3 = deposit, 4 = exit, 5 = trans. history
	if b == "1":
		print("="*20+"Balance GUI"+"="*20+"\n")
		a = len("="*20+"Balance GUI"+"="*20)
		print("Your current balance is : "+str(user_balance)+" USD\n")
		print("="*a)
		input("Press any key to return")
		
	elif b == "2":
		c = input("Enter the amount you want to withdraw : ")
		if not is_number(c):
			print(c+" is not numeric !")
			continue
		if int(c) == 0:
			continue
		if float(c) > user_balance:
			print("You don't have enough money...")
			input("Press any key to return")
			continue
		d = input("Is the amount "+c+" correct ? (Y/N) ")
		
		if d.lower() == "y":
			c = round(float(c),2)
			user_balance -= c                                #balance -
			table_update("w",c,user_id,user_name,user_balance)   #table_update()
			time.sleep(0.8)
			print("Withdraw completed")
			print("Your current balance is :"+ str(user_balance))
			input("Press any key to return")
		else:
			continue
			
	elif b == "3":
		c = input("Enter the amount you want to deposit : ")
	
		if not is_number(c):
			print(c+" is not numeric !")
			continue
		c = round(float(c),2)
		user_balance += c                                #balance +
		table_update("d",c,user_id,user_name,user_balance)  #table_update()
		time.sleep(0.8)
		print("Deposit completed")
		print("Your current balance is :"+ str(user_balance))
		input("Press any key to return")
		
	elif b == "4":
		print("Bye Bye")
		break
	elif  b == "5":
		print("="*12+"Your transaction history"+"="*11+"\n")
		a = len("="*20+"Your transaction history"+"="*20)
		show_transaction(user_name,user_id)             		#show_transaction
		print("Your curet balance is : "+str(user_balance)+" USD\n")
	
	else:
		print("Invalid number..")
    